前陣子版上大大分享的題目 【SQL分享】 統計玩家遊戲連勝連敗的資料，覺得還蠻有趣的，看了下面的留言才知道，原來這種題型叫做 連續範圍問題，剛好最近也有遇到類似的問題，分享給大家玩看看。

題目:

下方為使用者的購買清單。

欄位說明:

• UserNo: 使用者代碼
• StoreNo: 店家代碼
• ProductNo: 商品代碼
• Order: 購買順序

現在希望依照使用者的購買順序排序店家，相同店家為同一序位，不過如果相同店家中間穿插其他店家則為不同序位。

期望的結果:

測試資料:

DECLARE @BuyList TABLE
(
	UserNo NVARCHAR(10),
	StoreNo NVARCHAR(10),
	ProductNo NVARCHAR(10),
	[Order] INT
)
INSERT INTO @BuyList
	(UserNo, StoreNo, ProductNo, [Order])
VALUES
	('001', 'S01', 'P01', 1),
	('001', 'S01', 'P02', 2),
	('001', 'S02', 'P02', 3),
	('001', 'S02', 'P03', 4),
	('001', 'S02', 'P04', 5),
	('001', 'S01', 'P05', 6),
	('001', 'S01', 'P06', 7),
	('001', 'S01', 'P07', 8),
	('002', 'S01', 'P08', 1),
	('002', 'S02', 'P09', 2),
	('003', 'S01', 'P01', 1),
	('003', 'S02', 'P01', 2),
	('003', 'S02', 'P02', 3)

解法:

這題的重點在於如何去比較上一筆和下一筆的店家是否相同，發現 LAG 函數 可以達到此需求。

首先利用 LAG 函數比較上一筆的店家，如果相同就將標記 Diff 為 0，不相同為 1。

| UserNo | StoreNo | Order | Diff |
|--------|---------|-------|------|
|   001  |   S01   |   1   |   1  |
|   001  |   S01   |   2   |   0  |
|   001  |   S02   |   3   |   1  |

接著只需將 Diff 依序累加就是期望的結果。

| UserNo | StoreNo | Order | Diff | Sum |
|--------|---------|-------|------|-----|
|   001  |   S01   |   1   |   1  |  1  |
|   001  |   S01   |   2   |   0  |  1  |
|   001  |   S02   |   3   |   1  |  2  |

語法1

SELECT T.*,
(
    SELECT SUM(Diff)
    FROM(
        SELECT *, CASE WHEN (LAG(StoreNo) OVER(ORDER BY [Order]))
			=StoreNo THEN 0 ELSE 1 END Diff
        FROM @BuyList AS S
        WHERE S.UserNo=T.UserNo 
    ) AS S
    WHERE S.[Order] <= T.[Order]
) AS StoreOrder
FROM @BuyList AS T
ORDER BY UserNo, [Order]

語法2，效能更好

;WITH CTE AS 
(
    SELECT *, CASE WHEN LAG((UserNo + StoreNo)) 
		OVER (ORDER BY UserNo, [Order])=(UserNo + StoreNo) 
		THEN 0 ELSE 1 END Diff
    FROM @BuyList
)

SELECT UserNo, StoreNo, ProductNo, [Order],
    SUM(Diff) 
        OVER (PARTITION BY UserNo ORDER BY UserNo, [Order]) AS StoreOrder
FROM CTE
ORDER BY UserNo, [Order]

結語:

最後附上線上測試的連結，感謝大家觀看。

SQL1
SQL2

參考文章:

[SQL連續範圍] 數字，日期連續範圍